Imports System.Data.SqlClient
Imports System.Text

Friend Class DBBlockLog_Factory
    Inherits DBBase

    Public Function AverageDailyBlockingCounts( _
                ByVal sWeekBeginning As String, _
                ByVal lDatabaseID As Long) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageDailyBlockCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DateBeginning", SqlDbType.DateTime)
        oParm.Value = sWeekBeginning
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AverageDayBlockingCounts( _
                ByVal sDay As String, _
                ByVal lDatabaseID As Long) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageDayBlockCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@Date", SqlDbType.DateTime)
        oParm.Value = sDay
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AverageWeeklyBlockingCounts( _
                    ByVal lCountOfWeeks As Long, _
                    ByVal lDatabaseID As Long) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageWeeklyBlockCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@CountOfWeeks", SqlDbType.Int)
        oParm.Value = lCountOfWeeks
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AllDetailForPeriod( _
                    ByVal sStartDate As String, _
                    ByVal sEndDate As String, _
                    ByVal lDatabaseID As Long) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("   a.[Blocked_SPID],")
            .Append("   a.[Blocked_Status], ")
            .Append("   a.[Blocked_HostName],")
            .Append("   a.[Blocked_ProgramName],")
            .Append("   a.[Blocked_Command],")
            .Append("   a.[Blocked_LoginName],")
            .Append("   a.[Blocking_SPID],")
            .Append("   a.[Blocking_Status],")
            .Append("   a.[Blocking_HostName],")
            .Append("   a.[Blocking_ProgramName],")
            .Append("   a.[Blocking_Command],")
            .Append("   a.[Blocking_LoginName], ")
            .Append("   min(a.[CreateDate]) 'FromDate',")
            .Append("   max(a.[CreateDate]) 'ToDate'")
            .Append(" FROM")
            .Append("   blockinghistory a")
            .Append(" where")
            .Append("   a.[databaseid] = " & lDatabaseID)
            .Append("   and a.[CreateDate] between " & ValueToDBValue(sStartDate) & " and " & ValueToDBValue(sEndDate))
            .Append(" group by")
            .Append("   a.[Blocked_SPID], ")
            .Append("   a.[Blocked_Status],")
            .Append("   a.[Blocked_HostName],")
            .Append("   a.[Blocked_ProgramName],")
            .Append("   a.[Blocked_Command],")
            .Append("   a.[Blocked_LoginName],")
            .Append("   a.[Blocking_SPID],")
            .Append("   a.[Blocking_Status],")
            .Append("   a.[Blocking_HostName],")
            .Append("   a.[Blocking_ProgramName],")
            .Append("   a.[Blocking_Command],")
            .Append("   a.[Blocking_LoginName]")
            .Append(" order by")
            .Append("   min(a.[CreateDate])")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString)
    End Function

    Public Sub RemoveExpiredData(ByVal iExpirationPeriod As Integer)
        Dim sSQL As New StringBuilder("delete from dbo.BlockingHistory")
        sSQL.Append(" where")
        sSQL.Append("   datediff(day,[CreateDate],current_timestamp)>" & iExpirationPeriod)

        MyBase.ExecuteNonQuery(sSQL.ToString)
    End Sub
End Class
